package service;

import java.sql.*;

import db.DBUtil;
import model.*;

public class StudentManager {

    //添加学生
    public void addStudent(String student_no, String name, String major) {

        //2、创建sql
        String sql = "insert into student(student_no,name,major) values(?,?,?)";
        System.out.println("");

        //1、连接数据库
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, student_no);
            ps.setString(2, name);
            ps.setString(3, major);

            ps.executeUpdate();

            System.out.println("学生插入成功：" + name);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    //添加课程
    public void addCourse(String course_no, String name, double credit) {

        String sql = "insert into course(course_no,name,credit) values(?,?,?)";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, course_no);
            ps.setString(2, name);
            ps.setDouble(3, credit);

            ps.executeUpdate();

            System.out.println("成功添加课程：" + name);


        } catch (SQLException e) {
            e.printStackTrace();
        }


    }


    //添加成绩
    public void addGrade(Grade g) {

        String sql = "insert into grade(student_no,course_no,usual_score,final_score,total_score) values(?,?,?,?,?)";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, g.getStudentNo());
            ps.setString(2, g.getCourseNo());
            ps.setDouble(3, g.getUsualScore());
            ps.setDouble(4, g.getFinalScore());
            ps.setDouble(5, g.getTotalScore());

            ps.executeUpdate();

            System.out.println("成功添加成绩：" + g.getStudentNo());


        } catch (SQLException e) {
            e.printStackTrace();
        }


    }


    //根据学号查询成绩

    public void printReport(String student_no) {
        String sql = "select g.student_no, s.name, g.course_no, c.name as course, usual_score, final_score, total_score from grade g join student s on g.student_no = s.student_no join course c on g.course_no = c.course_no where g.student_no = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, student_no);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                rs.getString("student_no");
                String name = rs.getString("name");
                String course_no = rs.getString("course_no");
                String course = rs.getString("course");
                double usual_score = rs.getDouble("usual_score");
                double final_score = rs.getDouble("final_score");
                double total_score = rs.getDouble("total_score");

                System.out.printf("%s\t%s\t%.2f分\t平时分:%.2f\t期末分：%.2f\t总评分:%.2f", name, course, usual_score, final_score, total_score);


            }


        } catch (SQLException e) {
            e.printStackTrace();
        }


    }


}
